package homelesspartners.server;

import homelesspartners.client.model.City;
import homelesspartners.client.model.Gift;
import homelesspartners.client.model.Shelter;
import homelesspartners.client.model.Story;
import homelesspartners.client.model.ThankYou;
import homelesspartners.client.model.ThankYouPages;
import homelesspartners.client.model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import ashbyutils.email.Email;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

public class Data {

	private static ArrayList stats = null;
	private static ArrayList cities = null;

	private static final String NUM = "NUM";
	private static final String USER_ID = "USER_ID";
	private static final String SH_THEY_NEED = "SH.THEY_NEED";
	private static final String SH_THEY_DO = "SH.THEY_DO";
	private static final String SH_PHONE = "SH.PHONE";
	private static final String SH_ADDRESS = "SH.ADDRESS";
	private static final String SH_ADDITIONAL_DETAILS = "SH.ADDITIONAL_DETAILS";
	private static final String SH_NAME = "SH.NAME";
	private static final String SH_ID = "SH.ID";
	private static final String S_GENDER = "S.GENDER";
	private static final String S_LAST_INITIAL = "S.LAST_INITIAL";
	private static final String S_ASSIGNED_ID = "S.ASSIGNED_ID";
	private static final String S_FIRST_NAME = "S.FIRST_NAME";
	private static final String S_ID = "S.ID";
	private static final String G_DESCRIPTION = "G.DESCRIPTION";
	private static final String ROLE = "ROLE";
	private static final String PASSWORD = "PASSWORD";
	private static final String G_ID = "G.ID";
	private static final String DESCRIPTION = "DESCRIPTION";
	private static final String STORY_DETAILS = "STORY_DETAILS";
	private static final String LAST_INITIAL = "LAST_INITIAL";
	private static final String GENDER = "GENDER";
	private static final String ASSIGNED_ID = "ASSIGNED_ID";
	private static final String FIRST_NAME = "FIRST_NAME";
	private static final String ADDITIONAL_DETAILS = "ADDITIONAL_DETAILS";
	private static final String THEY_NEED = "THEY_NEED";
	private static final String THEY_DO = "THEY_DO";
	private static final String PHONE = "PHONE";
	private static final String ID_FORMAT = "ID_FORMAT";
	private static final String ADDRESS = "ADDRESS";
	private static final String FK_CITY_ID = "FK_CITY_ID";
	private static final String NAME = "NAME";
	private static final String ID = "ID";

	private static final String SQL_SELECT_GIFTS_BY_STORY =
			"select * from HOMELESSPARTNERS.GIFTS G left join HOMELESSPARTNERS.PLEDGES P on G.ID=P.FK_GIFT_ID left join HOMELESSPARTNERS.USERS U on P.FK_USER_ID=U.ID where FK_STORIES_ID=?";
	private static final String SQL_SELECT_PAGE_OF_STORIES =
			"select * from HOMELESSPARTNERS.STORIES where FK_SHELTER_ID=? order by SORT_ORDER desc limit "
					+ Shelter.PAGE_SIZE + " offset ?";
	private static final String SQL_UPDATE_SHELTER =
			"update HOMELESSPARTNERS.SHELTERS set NAME=?, ADDRESS=?, PHONE=?, WEBSITE=?, ID_FORMAT=?, THEY_DO=?, THEY_NEED=?, ADDITIONAL_DETAILS=? where ID=?";
	private static final String SQL_DELETE_SHELTER = "delete from HOMELESSPARTNERS.SHELTERS where ID=?";
	private static final String SQL_COUNT_STORIES_BY_SHELTER =
			"select count(*) as NUM from HOMELESSPARTNERS.STORIES where FK_SHELTER_ID=?";
	private static final String SQL_GET_MAX_SHELTER_ID = "select max(ID) as ID from HOMELESSPARTNERS.SHELTERS";
	private static final String SQL_INSERT_SHELTER =
			"insert into HOMELESSPARTNERS.SHELTERS (FK_CITY_ID, NAME, ADDRESS, PHONE, WEBSITE, ID_FORMAT, THEY_DO, THEY_NEED, ADDITIONAL_DETAILS) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
	private static final String SQL_UPDATE_USER_ROLE = "update HOMELESSPARTNERS.USERS set ROLE=? where USER_ID=?";
	private static final String SQL_UPDATE_USER_PASSWORD = "update HOMELESSPARTNERS.USERS set PASSWORD=? where ID=?";
	private static final String SQL_DELETE_GIFT = "delete from HOMELESSPARTNERS.GIFTS where ID=?";
	private static final String SQL_UPDATE_GIFT = "update HOMELESSPARTNERS.GIFTS set DESCRIPTION=? where ID=?";
	private static final String SQL_UPDATE_STORY =
			"update HOMELESSPARTNERS.STORIES set FIRST_NAME=?, LAST_INITIAL=?, GENDER=?, ASSIGNED_ID=?, STORY_DETAILS=? where ID=?";
	private static final String SQL_DELETE_STORY = "delete from HOMELESSPARTNERS.STORIES where ID=?";
	private static final String SQL_SELECT_USER_PLEDGES_BY_STORY =
			"select U.USER_ID from HOMELESSPARTNERS.PLEDGES P, HOMELESSPARTNERS.GIFTS G, HOMELESSPARTNERS.USERS U where G.FK_STORIES_ID=? and G.ID=P.FK_GIFT_ID and P.FK_USER_ID=U.ID";
	private static final String SQL_GET_MAX_GIFT_ID = "select max(ID) as ID from HOMELESSPARTNERS.GIFTS";
	private static final String SQL_INSERT_GIFT =
			"insert into HOMELESSPARTNERS.GIFTS (FK_STORIES_ID, DESCRIPTION) values (?, ?)";
	private static final String SQL_GET_MAX_STORY_ID = "select max(ID) as ID from HOMELESSPARTNERS.STORIES";
	private static final String SQL_INSERT_STORY =
			"insert into HOMELESSPARTNERS.STORIES (FK_SHELTER_ID, FIRST_NAME, LAST_INITIAL, GENDER, ASSIGNED_ID, STORY_DETAILS) values (?, ?, ?, ?, ?, ?)";
	private static final String SQL_INSERT_STORY_CHECK_DUPLICATES =
			"select * from HOMELESSPARTNERS.STORIES WHERE FK_SHELTER_ID=? AND ((FIRST_NAME=? AND LAST_INITIAL=?) OR (FIRST_NAME=? AND ASSIGNED_ID=?) OR (LAST_INITIAL=? AND ASSIGNED_ID=?))";
	private static final String SQL_SELECT_SHOPPING_LIST =
			"select * from HOMELESSPARTNERS.GIFTS G, HOMELESSPARTNERS.PLEDGES P, HOMELESSPARTNERS.STORIES S, HOMELESSPARTNERS.SHELTERS SH, HOMELESSPARTNERS.USERS U where P.FK_USER_ID=? and G.ID=P.FK_GIFT_ID and G.FK_STORIES_ID=S.ID and S.FK_SHELTER_ID=SH.ID and P.FK_USER_ID=U.ID order by G.ID";
	private static final String SQL_CREATE_USER =
			"insert into HOMELESSPARTNERS.USERS (USER_ID, PASSWORD) values (?, ?)";
	private static final String SQL_LOGIN = "select * from HOMELESSPARTNERS.USERS where USER_ID=?";
	private static final String SQL_REMOVE_PLEDGE =
			"delete from HOMELESSPARTNERS.PLEDGES where FK_GIFT_ID=? and FK_USER_ID=?";
	private static final String SQL_RECORD_PLEDGE =
			"insert into HOMELESSPARTNERS.PLEDGES (FK_GIFT_ID, FK_USER_ID) values (?, ?)";
	private static final String SQL_SELECT_STORIES =
			"select * from HOMELESSPARTNERS.STORIES where FK_SHELTER_ID=? and ASSIGNED_ID like ? and FIRST_NAME like ? order by FIRST_NAME, ID";
	private static final String SQL_SELECT_SHELTERS_BY_CITY =
			"select * from HOMELESSPARTNERS.SHELTERS where FK_CITY_ID=? order by NAME";
	private static final String SQL_SELECT_CITIES = "select * from HOMELESSPARTNERS.CITIES order by NAME";
	private static final String SQL_SELECT_PASSWORD = "select PASSWORD from HOMELESSPARTNERS.USERS where USER_ID=?";
	private static final String SQL_INSERT_THANKYOU =
			"insert into HOMELESSPARTNERS.THANKS (TITLE, THANKS) VALUES (?, ?)";
	private static final String SQL_UPDATE_THANKYOU = "update HOMELESSPARTNERS.THANKS set THANKS=?, TITLE=? where ID=?";
	private static final String SQL_DELETE_THANKYOU = "delete from HOMELESSPARTNERS.THANKS where ID=?";
	private static final String SQL_SELECT_THANK_YOU_PAGE =
			"select * from HOMELESSPARTNERS.THANKS order by TIME_STAMP desc limit " + ThankYouPages.PAGE_SIZE
					+ " offset ?";
	private static final String SQL_SEARCH_THANKS = "select * from HOMELESSPARTNERS.THANKS where TITLE like ?";
	private static final String SQL_SELECT_STATS_GIFTED =
			"SELECT COUNT(S.ID) AS THECOUNT FROM HOMELESSPARTNERS.STORIES S WHERE S.ID IN (SELECT S.ID FROM HOMELESSPARTNERS.STORIES S, HOMELESSPARTNERS.GIFTS G, HOMELESSPARTNERS.PLEDGES P WHERE S.ID=G.FK_STORIES_ID AND G.ID=P.FK_GIFT_ID)";
	private static final String SQL_SELECT_STATS_UNGIFTED =
			"SELECT C.NAME AS CITY, COUNT(S.ID) AS THECOUNT FROM HOMELESSPARTNERS.CITIES C, HOMELESSPARTNERS.SHELTERS SH, HOMELESSPARTNERS.STORIES S WHERE C.ID=SH.FK_CITY_ID AND SH.ID=S.FK_SHELTER_ID AND S.ID NOT IN (SELECT S.ID FROM HOMELESSPARTNERS.STORIES S, HOMELESSPARTNERS.GIFTS G, HOMELESSPARTNERS.PLEDGES P WHERE S.ID=G.FK_STORIES_ID AND G.ID=P.FK_GIFT_ID) GROUP BY C.ID ORDER BY C.NAME ASC";

	private static Object addStoryLock = new Object();
	private static Object addGiftLock = new Object();
	private static Object addShelterLock = new Object();
	private static Object getStatsLock = new Object();
	private static Object getCitiesLock = new Object();

	public ArrayList getCities() throws DataException {

		if (cities != null) {
			return cities;
		}
		synchronized (getCitiesLock) {
			if (cities != null) {
				return cities;
			}

			Connection connection = getConnection();
			try {
				PreparedStatement statement = connection.prepareStatement(SQL_SELECT_CITIES);
				ResultSet resultSet = statement.executeQuery();
				ArrayList cities = new ArrayList();
				while (resultSet.next()) {
					City city = new City();
					city.setId(resultSet.getInt(ID));
					city.setName(resultSet.getString(NAME));
					cities.add(city);
				}
				close(resultSet, statement, connection);
				this.cities = cities;
				return cities;
			} catch (Exception e) {
				close(null, null, connection);
				throw new DataException(e);
			}
		}

	}

	private void close(ResultSet resultSet, PreparedStatement statement, Connection connection) {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private Connection getConnection() throws DataException {
		try {
			InitialContext context = new InitialContext();
			DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/homelesspartners");
			Connection connection = dataSource.getConnection();
			return connection;
		} catch (Exception e) {
			throw new DataException(e);
		}
	}

	public ArrayList<Shelter> getShelters(int cityId) throws DataException {
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_SELECT_SHELTERS_BY_CITY);
			statement.setInt(1, cityId);
			ResultSet resultSet = statement.executeQuery();
			ArrayList shelters = new ArrayList();
			while (resultSet.next()) {
				Shelter shelter = new Shelter();
				shelter.setId(resultSet.getInt(ID));
				shelter.setName(resultSet.getString(NAME));
				shelter.setCityId(resultSet.getInt(FK_CITY_ID));
				shelter.setAddress(resultSet.getString(ADDRESS));
				shelter.setPhone(resultSet.getString(PHONE));
				shelter.setWebsite(resultSet.getString("WEBSITE"));
				shelter.setIdFormat(resultSet.getString(ID_FORMAT));
				shelter.setWhatTheyDo(resultSet.getString(THEY_DO));
				shelter.setWhatTheyNeed(resultSet.getString(THEY_NEED));
				shelter.setAdditionalDetails(resultSet.getString(ADDITIONAL_DETAILS));
				shelters.add(shelter);
			}
			close(resultSet, statement, connection);
			return shelters;
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	public ArrayList getStories(int shelterId, String idQuery, String nameQuery) throws DataException {
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_SELECT_STORIES);
			PreparedStatement giftStatement = connection.prepareStatement(SQL_SELECT_GIFTS_BY_STORY);

			statement.setInt(1, shelterId);

			if (idQuery == null) {
				idQuery = "";
			}
			if (nameQuery == null) {
				nameQuery = "";
			}
			idQuery = "%" + idQuery + "%";
			nameQuery = "%" + nameQuery + "%";

			statement.setString(2, idQuery);
			statement.setString(3, nameQuery);

			ResultSet resultSet = statement.executeQuery();
			ArrayList stories = new ArrayList();
			while (resultSet.next()) {
				Story story = parseStory(resultSet);
				stories.add(story);

				giftStatement.setInt(1, story.getId());
				ResultSet giftResults = giftStatement.executeQuery();
				while (giftResults.next()) {
					Gift gift = new Gift();
					gift.setDescription(giftResults.getString(DESCRIPTION));
					gift.setId(giftResults.getInt(G_ID));
					String userName = giftResults.getString(USER_ID);
					gift.setDonor(userName);
					story.addGift(gift);
				}
			}
			close(resultSet, statement, connection);
			return stories;
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	private Story parseStory(ResultSet resultSet) throws SQLException {
		Story story = new Story();
		int currentId = resultSet.getInt(ID);
		story.setId(currentId);
		story.setFirstName(resultSet.getString(FIRST_NAME));
		story.setAssignedId(resultSet.getString(ASSIGNED_ID));
		story.setGender(resultSet.getString(GENDER).charAt(0));
		story.setLastInitial(resultSet.getString(LAST_INITIAL));
		story.setStory(resultSet.getString(STORY_DETAILS));
		return story;
	}

	public boolean recordPledge(int giftId, int userId) throws DataException {
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_RECORD_PLEDGE);
			statement.setInt(1, giftId);
			statement.setInt(2, userId);
			int rows = statement.executeUpdate();
			close(null, statement, connection);
			if (rows > 0) {
				return true;
			} else {
				return false;
			}
		} catch (MySQLIntegrityConstraintViolationException mysqle) {
			close(null, null, connection);
			return false;
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	public boolean removePledge(int giftId, int userId) throws DataException {
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_REMOVE_PLEDGE);
			statement.setInt(1, giftId);
			statement.setInt(2, userId);
			int rows = statement.executeUpdate();
			close(null, statement, connection);
			if (rows == 1) {
				return true;
			} else {
				return false;
			}
		} catch (MySQLIntegrityConstraintViolationException mysqle) {
			return false;
		} catch (Exception e) {
			throw new DataException(e);
		}
	}

	public User login(String userId, String password) throws DataException {
		if (password == null) {
			return null;
		}
		if (userId != null) {
			userId = userId.toLowerCase();
		}
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_LOGIN);
			statement.setString(1, userId);
			ResultSet resultSet = statement.executeQuery();
			User user = null;
			while (resultSet.next()) {
				int id = resultSet.getInt(ID);
				String storedPassword = resultSet.getString(PASSWORD);
				if (password.equals(storedPassword)) {
					user = new User();
					user.setUserId(id);
					int role = resultSet.getInt(ROLE);
					user.setRole(role);
				}
			}
			close(resultSet, statement, connection);
			return user;
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	public User createNewUser(String userId, String password) throws DataException {
		if (userId != null) {
			userId = userId.toLowerCase();
		}
		Connection connection = getConnection();
		User user = new User();
		user.setEmailAddress(userId);
		user.setPassword(password);
		if (!user.isValid()) {
			DataException exception = new DataException(new Exception());
			exception
					.setMessage("A new user could not be created.  Please check the user id and password to ensure they are valid");
			throw exception;
		}
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_CREATE_USER);
			statement.setString(1, userId);
			statement.setString(2, password);
			int rows = statement.executeUpdate();
			close(null, statement, connection);
			if (rows == 1) {
				return login(userId, password);
			} else {
				return null;
			}
		} catch (MySQLIntegrityConstraintViolationException mysqle) {
			close(null, null, connection);
			throw new DuplicateDataException(mysqle);
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	public ArrayList getShoppingList(int userId) throws DataException {
		Connection connection = getConnection();
		try {
			PreparedStatement statement = connection.prepareStatement(SQL_SELECT_SHOPPING_LIST);
			statement.setInt(1, userId);
			ResultSet resultSet = statement.executeQuery();
			ArrayList gifts = new ArrayList();
			while (resultSet.next()) {
				Gift gift = new Gift();
				gift.setId(resultSet.getInt(G_ID));
				gift.setDescription(resultSet.getString(G_DESCRIPTION));
				String donor = resultSet.getString(USER_ID);
				gift.setDonor(donor);
				Story story = new Story();
				story.setId(resultSet.getInt(S_ID));
				story.setFirstName(resultSet.getString(S_FIRST_NAME));
				story.setAssignedId(resultSet.getString(S_ASSIGNED_ID));
				story.setLastInitial(resultSet.getString(S_LAST_INITIAL));
				story.setGender(resultSet.getString(S_GENDER).charAt(0));
				story.addGift(gift);
				Shelter shelter = new Shelter();
				shelter.setId(resultSet.getInt(SH_ID));
				shelter.setName(resultSet.getString(SH_NAME));
				shelter.setAdditionalDetails(resultSet.getString(SH_ADDITIONAL_DETAILS));
				shelter.setAddress(resultSet.getString(SH_ADDRESS));
				shelter.setPhone(resultSet.getString(SH_PHONE));
				shelter.setWebsite(resultSet.getString("WEBSITE"));
				shelter.setWhatTheyDo(resultSet.getString(SH_THEY_DO));
				shelter.setWhatTheyNeed(resultSet.getString(SH_THEY_NEED));
				shelter.addStory(story);
				gifts.add(gift);
			}
			close(resultSet, statement, connection);
			return gifts;
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
	}

	public ArrayList addInterviewCheckFirst(Story story) throws DataException {
		String checkDuplicatesSQL = SQL_INSERT_STORY_CHECK_DUPLICATES;
		Connection connection = null;
		ArrayList stories = new ArrayList();
		
		try {
			connection = getConnection();

			PreparedStatement duplicates = connection.prepareStatement(checkDuplicatesSQL);
			duplicates.setInt(1, story.getShelter().getId());
			duplicates.setString(2, story.getFirstName());
			duplicates.setString(3, story.getLastInitial());
			duplicates.setString(4, story.getFirstName());
			duplicates.setString(5, story.getAssignedId());
			duplicates.setString(6, story.getLastInitial());
			duplicates.setString(7, story.getAssignedId());
			ResultSet duplicateResults = duplicates.executeQuery();
			while(duplicateResults.next()) {
				Story aStory = parseStory(duplicateResults);
				stories.add(aStory);
			}
		} catch (Exception e) {
			throw new DataException(e);
		} finally {
			close(null, null, connection);
		}

		if(stories.size() == 0){
			stories.add(addInterview(story));
		} else {
			stories.add(story);
		}
		return stories;
	}

	public Story addInterview(Story story) throws DataException {
		String insertStorySQL = SQL_INSERT_STORY;
		String getStoryIDSQL = SQL_GET_MAX_STORY_ID;
		String insertGiftSQL = SQL_INSERT_GIFT;
		String getGiftIDSQL = SQL_GET_MAX_GIFT_ID;

		Connection connection = getConnection();

		try {

			PreparedStatement statement = connection.prepareStatement(insertStorySQL);
			statement.setInt(1, story.getShelter().getId());
			statement.setString(2, story.getFirstName());
			statement.setString(3, story.getLastInitial());
			statement.setString(4, "" + story.getGender());
			statement.setString(5, story.getAssignedId());
			statement.setString(6, story.getStory());

			PreparedStatement getIdStatement = connection.prepareStatement(getStoryIDSQL);
			ResultSet result = null;

			synchronized (addStoryLock) {
				int rows = statement.executeUpdate();
				if (rows != 1) {
					throw new Exception("Could not insert the story");
				}
				result = getIdStatement.executeQuery();

			}

			result.next();
			int id = result.getInt(ID);
			story.setId(id);

			// for each gift,
			// add the gift
			// figure out the gift id
			// set the gift id

			PreparedStatement addGiftStatement = connection.prepareStatement(insertGiftSQL);
			addGiftStatement.setInt(1, story.getId());
			PreparedStatement getGiftId = connection.prepareStatement(getGiftIDSQL);

			Gift[] gifts = story.getGifts();
			for (int x = 0; x < gifts.length && gifts[x] != null; x++) {
				Gift gift = gifts[x];

				addGiftStatement.setString(2, gift.getDescription());

				synchronized (addGiftLock) {
					int rows = addGiftStatement.executeUpdate();
					if (rows != 1) {
						throw new Exception();
					}
					result = getGiftId.executeQuery();
				}

				result.next();
				int giftid = result.getInt(ID);
				gift.setId(giftid);
			}
			close(result, statement, connection);
		} catch (Exception e) {
			close(null, null, connection);
			throw new DataException(e);
		}
		return story;
	}

	public boolean deleteStory(int storyId) throws DataException {

		String selectPledgesSQL = SQL_SELECT_USER_PLEDGES_BY_STORY;
		Connection connection = getConnection();
		PreparedStatement statement = null;
		ResultSet results = null;

		try {
			statement = connection.prepareStatement(selectPledgesSQL);
			statement.setInt(1, storyId);
			results = statement.executeQuery();
			StringBuffer buffer = new StringBuffer();

			while (results.next()) {
				buffer.append(results.getString(USER_ID));
				buffer.append(" ");
			}
			if (buffer.length() != 0) {
				DataException exception = new DataException(new Exception());
				exception
						.setMessage("The following users have pledged gifts for this person and must be notified first: "
								+ buffer.toString());
				throw exception;
			}
		} catch (SQLException e) {
			close(results, statement, null);
			throw new DataException(e);
		} finally {
			close(results, statement, null);
		}

		try {
			statement = connection.prepareStatement(SQL_DELETE_STORY);
			statement.setInt(1, storyId);
			int rows = statement.executeUpdate();
			if (rows == 1) {
				return true;
			}
			return false;

		} catch (SQLException e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, connection);
		}
	}

	public Story updateStory(Story story) throws DataException {
		String updateStorySQL = SQL_UPDATE_STORY;
		String updateGiftSQL = SQL_UPDATE_GIFT;
		String insertGiftSQL = SQL_INSERT_GIFT;
		String getGiftIDSQL = SQL_GET_MAX_GIFT_ID;

		/*
		 * first update the story for each gift if is deleted if id > 0 delete
		 * it remove from story for each gift that is left if id < 0 add it else
		 * update it
		 */

		Connection connection = getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(updateStorySQL);
			statement.setString(1, story.getFirstName());
			statement.setString(2, story.getLastInitial());
			statement.setString(3, "" + story.getGender());
			statement.setString(4, story.getAssignedId());
			statement.setString(5, story.getStory());
			statement.setInt(6, story.getId());
			int rows = statement.executeUpdate();
			if (rows != 1) {
				throw new DataException(new Exception("Could not update the story fields."));
			}
		} catch (Exception e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, null);
		}

		PreparedStatement addGiftStatement = null;
		PreparedStatement updateGiftStatement = null;
		PreparedStatement getGiftId = null;
		ResultSet result = null;

		try {
			updateGiftStatement = connection.prepareStatement(updateGiftSQL);
			addGiftStatement = connection.prepareStatement(insertGiftSQL);
			addGiftStatement.setInt(1, story.getId());
			getGiftId = connection.prepareStatement(getGiftIDSQL);
			Gift[] gifts = story.getGifts();
			for (int x = 0; x < gifts.length && gifts[x] != null; x++) {
				Gift gift = gifts[x];

				if (gift.getId() < 0) {

					addGiftStatement.setString(2, gift.getDescription());

					synchronized (addGiftLock) {
						int rows = addGiftStatement.executeUpdate();
						if (rows != 1) {
							throw new Exception("Could not add the gift " + gift.getDescription());
						}
						result = getGiftId.executeQuery();
					}

					result.next();
					int giftid = result.getInt(ID);
					gift.setId(giftid);
				} else {
					if (gift.getDescription() == null || gift.getDescription().length() == 0) {
						if (!deleteGift(gift.getId())) {
							throw new DataException(new Exception("Could not update gift #" + x));
						}
						story.getGifts()[x] = null;
					} else {
						updateGiftStatement.setString(1, gift.getDescription());
						updateGiftStatement.setInt(2, gift.getId());
						int rows = updateGiftStatement.executeUpdate();
						if (rows != 1) {
							throw new DataException(new Exception("Could not update gift " + gift.getDescription()));
						}
					}
				}

			}

		} catch (Exception e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, connection);
		}
		return story;
	}

	public Boolean updateVolunteer(User volunteer) throws DataException {

		// try to update the user
		// if number of rows < 1
		// then throw the exception

		if (volunteer.getEmailAddress() != null) {
			volunteer.setEmailAddress(volunteer.getEmailAddress().toLowerCase());
		}
		String updateUserSQL = SQL_UPDATE_USER_ROLE;
		Connection connection = getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(updateUserSQL);
			statement.setInt(1, volunteer.getRole());
			statement.setString(2, volunteer.getEmailAddress());
			int rows = statement.executeUpdate();
			if (rows == 1) {
				return true;
			}
			throw new DataException(new Exception(
					"The user does not exist, please enter a password and click 'Create' to create it."));

		} catch (Exception e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, connection);
		}
	}

	public Shelter addShelter(Shelter shelter) throws DataException {
		String insertShelterSQL = SQL_INSERT_SHELTER;
		String getShelterIDSQL = SQL_GET_MAX_SHELTER_ID;
		Connection connection = getConnection();
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			statement = connection.prepareStatement(insertShelterSQL);
			statement.setInt(1, shelter.getCityId());
			statement.setString(2, shelter.getName());
			statement.setString(3, shelter.getAddress());
			statement.setString(4, shelter.getPhone());
			statement.setString(5, shelter.getWebsite());
			statement.setString(6, shelter.getIdFormat());
			statement.setString(7, shelter.getWhatTheyDo());
			statement.setString(8, shelter.getWhatTheyNeed());
			statement.setString(9, shelter.getAdditionalDetails());

			PreparedStatement getIdStatement = connection.prepareStatement(getShelterIDSQL);

			synchronized (addShelterLock) {
				int rows = statement.executeUpdate();
				if (rows != 1) {
					throw new Exception("Could not insert the shelter");
				}
				result = getIdStatement.executeQuery();

			}

			result.next();
			int id = result.getInt(ID);
			shelter.setId(id);
		} catch (Exception e) {
			close(result, statement, connection);
			throw new DataException(e);
		} finally {
			close(result, statement, connection);
		}
		return shelter;
	}

	public Boolean deleteShelter(int shelterId) throws DataException {
		Connection connection = getConnection();
		PreparedStatement statement = null;
		ResultSet result = null;
		try {
			statement = connection.prepareStatement(SQL_COUNT_STORIES_BY_SHELTER);
			statement.setInt(1, shelterId);
			result = statement.executeQuery();
			result.next();
			int storiesCount = result.getInt(NUM);
			if (storiesCount != 0) {
				DataException exception = new DataException(new Exception());
				exception.setMessage("There are stories for this shelter.  You must delete all the stories first.");
				throw exception;
			}
			close(null, statement, null);
			statement = connection.prepareStatement(SQL_DELETE_SHELTER);
			statement.setInt(1, shelterId);
			int rows = statement.executeUpdate();
			if (rows == 1) {
				return true;
			}
			return false;
		} catch (SQLException e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, connection);
		}
	}

	public Shelter updateShelter(Shelter shelter) throws DataException {
		String updateShelterSQL = SQL_UPDATE_SHELTER;

		Connection connection = getConnection();
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(updateShelterSQL);
			statement.setString(1, shelter.getName());
			statement.setString(2, shelter.getAddress());
			statement.setString(3, shelter.getPhone());
			statement.setString(4, shelter.getWebsite());
			statement.setString(5, shelter.getIdFormat());
			statement.setString(6, shelter.getWhatTheyDo());
			statement.setString(7, shelter.getWhatTheyNeed());
			statement.setString(8, shelter.getAdditionalDetails());
			statement.setInt(9, shelter.getId());
			int rows = statement.executeUpdate();
			if (rows != 1) {
				throw new DataException(new Exception("Could not update the shelter fields."));
			}
		} catch (Exception e) {
			close(null, statement, connection);
			throw new DataException(e);
		} finally {
			close(null, statement, null);
		}

		return shelter;
	}

	public ArrayList<Story> getPageOfStories(int shelterId, int offset) throws DataException {

		Connection connection = getConnection();

		PreparedStatement storyStatement = null;
		PreparedStatement giftsStatement = null;
		ResultSet storyResults = null;
		ResultSet giftResults = null;

		ArrayList<Story> stories = new ArrayList();
		Shelter shelter = new Shelter();
		shelter.setId(shelterId);

		try {
			storyStatement = connection.prepareStatement(SQL_SELECT_PAGE_OF_STORIES);
			storyStatement.setInt(1, shelterId);
			storyStatement.setInt(2, offset);
			storyResults = storyStatement.executeQuery();
			giftsStatement = connection.prepareStatement(SQL_SELECT_GIFTS_BY_STORY);
			while (storyResults.next()) {
				Story story = new Story();
				story.setId(storyResults.getInt(ID));
				story.setFirstName(storyResults.getString(FIRST_NAME));
				story.setAssignedId(storyResults.getString(ASSIGNED_ID));
				story.setGender(storyResults.getString(GENDER).charAt(0));
				story.setLastInitial(storyResults.getString(LAST_INITIAL));
				story.setStory(storyResults.getString(STORY_DETAILS));
				stories.add(story);
				story.setShelter(shelter);
				giftsStatement.setInt(1, story.getId());
				giftResults = giftsStatement.executeQuery();
				while (giftResults.next()) {
					Gift gift = new Gift();
					gift.setDescription(giftResults.getString(DESCRIPTION));
					gift.setId(giftResults.getInt(G_ID));
					String userName = giftResults.getString(USER_ID);
					gift.setDonor(userName);
					story.addGift(gift);
				}
			}
		} catch (Exception e) {
			close(storyResults, storyStatement, null);
			close(giftResults, giftsStatement, connection);
			throw new DataException(e);
		} finally {
			close(storyResults, storyStatement, null);
			close(giftResults, giftsStatement, connection);
		}

		return stories;
	}

	public Boolean deleteGift(int giftId) throws DataException {

		Connection connection = getConnection();

		PreparedStatement giftsStatement = null;

		try {
			giftsStatement = connection.prepareStatement(SQL_DELETE_GIFT);
			giftsStatement.setInt(1, giftId);
			int rows = giftsStatement.executeUpdate();
			if (rows < 1) {
				close(null, giftsStatement, connection);
				return new Boolean(false);
			}
		} catch (Exception e) {
			close(null, giftsStatement, connection);
			throw new DataException(e);
		}

		return new Boolean(true);
	}

	public void sendPassword(String emailAddress) throws DataException {
		Connection connection = getConnection();

		PreparedStatement getPasswordStatement = null;
		ResultSet result = null;

		try {
			getPasswordStatement = connection.prepareStatement(SQL_SELECT_PASSWORD);
			getPasswordStatement.setString(1, emailAddress.toLowerCase());
			result = getPasswordStatement.executeQuery();
			result.next();
			String password = result.getString("PASSWORD");
			if (password == null || password.length() < 4) {
				throw new Exception();
			}
			Email email = Email.createInstance();
			email.setTo(emailAddress);
			email.setFrom("homelesspartners@gmail.com");
			email.setSubject("Account Information for Homeless Partners");
			email.setHtml(true);
			email
					.setBody("You are receiving this email because you, or someone pretending to be you, indicated that you forgot your password for the <a href='http://homelesspartners.com'>Homeless Partners</a> website.  Your password is below.<br /><br />"
							+ password
							+ "<br/><br/>If you have any further questions, please contact us by replying to this email.  Thanks for you interest in helping the homeless!<br />Homeless Partners Website Support");
			email.send();
			close(result, getPasswordStatement, connection);
		} catch (Exception e) {
			close(result, getPasswordStatement, connection);
			throw new DataException(e);
		}
	}

	public Boolean createVolunteer(User volunteer) throws DataException {
		// create the account
		// update the role
		try {
			createNewUser(volunteer.getEmailAddress(), volunteer.getPassword());
		} catch (DuplicateDataException e) {
			throw new DataException(new Exception(
					"The account already exists.  Please remove the password and click 'Update' instead."));
		}
		return updateVolunteer(volunteer);
	}

	public Boolean updatePassword(User user, String password) throws DataException {
		Connection connection = getConnection();

		PreparedStatement updatePasswordStatement = null;

		try {
			updatePasswordStatement = connection.prepareStatement(SQL_UPDATE_USER_PASSWORD);
			updatePasswordStatement.setString(1, password);
			updatePasswordStatement.setInt(2, user.getUserId());
			int rows = updatePasswordStatement.executeUpdate();
			if (rows < 1) {
				close(null, updatePasswordStatement, connection);
				return new Boolean(false);
			}
		} catch (Exception e) {
			close(null, updatePasswordStatement, connection);
			throw new DataException(e);
		}

		return new Boolean(true);

	}

	public boolean addThankYouNote(ThankYou thankyou) throws DataException {

		Connection connection = getConnection();

		ResultSet results = null;

		try {
			PreparedStatement insertStatement = connection.prepareStatement(SQL_INSERT_THANKYOU);
			insertStatement.setString(1, thankyou.getTitle());
			insertStatement.setString(2, thankyou.getThankyou());
			int rows = insertStatement.executeUpdate();
			close(results, insertStatement, connection);
			if (rows == 1) {
				return true;
			}
			throw new Exception();
		} catch (Exception e) {
			e.printStackTrace();
			close(null, null, connection);
			Exception e2 =
					new Exception("Could not add the thank you note for this story.  Please see logs for details");
			throw new DataException(e2);

		}

	}

	public boolean updateThankYouNote(ThankYou thankyou) throws DataException {

		Connection connection = getConnection();

		ResultSet results = null;

		try {
			PreparedStatement updateStatement = connection.prepareStatement(SQL_UPDATE_THANKYOU);
			updateStatement.setString(1, thankyou.getThankyou());
			updateStatement.setString(2, thankyou.getTitle());
			updateStatement.setInt(3, thankyou.getId());
			int rows = updateStatement.executeUpdate();
			close(results, updateStatement, connection);
			if (rows == 1) {
				return true;
			}
			throw new Exception();
		} catch (Exception e) {
			e.printStackTrace();
			close(null, null, connection);
			Exception e2 =
					new Exception("Could not update the thank you note for this story.  Please see logs for details");
			throw new DataException(e2);

		}

	}

	public ArrayList<ThankYou> getPageOfThankYous(int offset) throws DataException {
		Connection connection = getConnection();

		PreparedStatement thankyouStatement = null;
		ResultSet thankyouResults = null;

		ArrayList<ThankYou> thanks = new ArrayList();

		try {
			thankyouStatement = connection.prepareStatement(SQL_SELECT_THANK_YOU_PAGE);
			thankyouStatement.setInt(1, offset);
			thankyouResults = thankyouStatement.executeQuery();
			while (thankyouResults.next()) {
				ThankYou thankyou = new ThankYou();
				thankyou.setStoryId(thankyouResults.getInt(ID));
				thankyou.setTitle(thankyouResults.getString("TITLE"));
				thankyou.setThankyou(thankyouResults.getString("THANKS"));
				thanks.add(thankyou);
			}
			return thanks;
		} catch (Exception e) {
			close(thankyouResults, thankyouStatement, null);
			throw new DataException(e);
		} finally {
			close(thankyouResults, thankyouStatement, null);
		}

	}

	public ArrayList getThankYous(String headerQuery) throws DataException {
		Connection connection = getConnection();

		PreparedStatement thankyouStatement = null;
		ResultSet thankyouResults = null;

		ArrayList<ThankYou> thanks = new ArrayList();

		try {
			thankyouStatement = connection.prepareStatement(SQL_SEARCH_THANKS);
			thankyouStatement.setString(1, "%" + headerQuery + "%");
			thankyouResults = thankyouStatement.executeQuery();
			while (thankyouResults.next()) {
				ThankYou thankyou = new ThankYou();
				thankyou.setStoryId(thankyouResults.getInt(ID));
				thankyou.setTitle(thankyouResults.getString("TITLE"));
				thankyou.setThankyou(thankyouResults.getString("THANKS"));
				thanks.add(thankyou);
			}
			return thanks;
		} catch (Exception e) {
			close(thankyouResults, thankyouStatement, null);
			throw new DataException(e);
		} finally {
			close(thankyouResults, thankyouStatement, null);
		}

	}

	public boolean deleteThankYouNote(int id) throws DataException {
		Connection connection = getConnection();

		ResultSet results = null;

		try {
			PreparedStatement deleteStatement = connection.prepareStatement(SQL_DELETE_THANKYOU);
			deleteStatement.setInt(1, id);
			int rows = deleteStatement.executeUpdate();
			close(results, deleteStatement, connection);
			if (rows == 1) {
				return true;
			}
			throw new Exception();
		} catch (Exception e) {
			close(null, null, connection);
			Exception e2 =
					new Exception("Could not update the thank you note for this story.  Please see logs for details");
			throw new DataException(e2);

		}
	}

	public ArrayList getThankYouStats() {
		if (stats != null) {
			return stats;
		}
		synchronized (getStatsLock) {
			if (stats != null) {
				return stats;
			}

			Connection connection = null;

			ResultSet results = null;

			try {
				connection = getConnection();
				PreparedStatement giftedCountStatement = connection.prepareStatement(SQL_SELECT_STATS_GIFTED);
				results = giftedCountStatement.executeQuery();
				results.next();
				int giftedCount = results.getInt("THECOUNT");
				close(results, giftedCountStatement, null);
				PreparedStatement ungiftedCountTableStatement = connection.prepareStatement(SQL_SELECT_STATS_UNGIFTED);
				results = ungiftedCountTableStatement.executeQuery();
				StringBuffer buffer = new StringBuffer();
				buffer.append("<table border='1'><tr><td><b>City</b></td><td><b>Remaining</b></td></tr>");
				while (results.next()) {
					buffer.append("<tr><td>");
					buffer.append(results.getString("CITY"));
					buffer.append("</td><td>");
					buffer.append(results.getString("THECOUNT"));
					buffer.append("</td></tr>");
				}
				buffer.append("</table>");
				close(results, ungiftedCountTableStatement, null);

				stats = new ArrayList();
				stats.add("" + giftedCount);
				stats.add(buffer.toString());
				return stats;
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				close(null, null, connection);
			}
		}
		return null;
	}
}
